USE GESTION2
go

IF OBJECT_ID('dbo.WASP_M0016_Carga_tabla') IS NOT NULL 
   BEGIN
      DROP PROCEDURE dbo.WASP_M0016_Carga_tabla 
      IF OBJECT_ID('dbo.WASP_M0016_Carga_tabla') 
      IS
      NOT NULL 
         PRINT '<<< FAILED DROPPING PROCEDURE dbo.WASP_M0016_Carga_tabla >>>' 
      ELSE
         PRINT '<<< DROPPED PROCEDURE dbo.WASP_M0016_Carga_tabla >>>' 
   END
go

SET ANSI_NULLS ON
go

SET QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE WASP_M0016_Carga_tabla(@Fecha_Inicio AS datetime,@fecha_termino AS datetime) 
AS
   BEGIN
      SELECT 	DISTINCT RTRIM(LTRIM(per.pfape1)) + ' ' + RTRIM(LTRIM(per.pfape2)) + ' ' + per.pfnom1 AS Nombre_Trabajador,
         RTRIM(LTRIM(per.pfndoc)) AS Rut_Trabajador,
         RTRIM(LTRIM(cast(CONVERT(varchar, per.Pffnac, 112) AS decimal(8, 0)))) as Fecha_Nacimiento,
         Sexo = 
         CASE per.Pfcant WHEN 'F' THEN '1' WHEN 'M' THEN '0' 
         END         ,
         NULL AS numero_cuotas,
         NULL AS prima_cobrada,
         aosuc,
         aooper,
         aocta,
         aopap,
         aomda,
         aopre 
      INTO	dbo.as_seg_vida_temp 
      FROM 	fsd611 AS a (nolock), fsd601 AS b (nolock), fsd010 AS c (nolock), 
      fsr008 rel (nolock), fsd002 per (nolock), fsd008 tp (nolock), JT73109 
      coloc 
      WHERE (a.ppimp11 = 101 
      OR a.ppimp12 = 101 
      OR a.ppimp13 = 101 
      OR a.ppimp14 = 101 
      OR a.ppimp15 = 101 
      OR a.ppimp16 = 101 
      OR a.ppimp17 = 101 
      OR a.ppimp18 = 101 
      OR a.ppimp19 = 101 ) 
      AND a.ppcta = c.aocta 
      AND c.aosuc = a.ppsuc 
      AND c.aooper = a.ppoper 
      AND c.aosbop = 0 
      AND coloc.JT73109FAP BETWEEN @Fecha_Inicio AND @fecha_termino 
      AND rel.Ctnro = c.aocta 
      AND rel.Pendoc = per.Pfndoc 
      AND tp.Ctnro = rel.Ctnro 
      AND a.pgcod = b.pgcod 
      AND a.ppmod = b.ppmod 
      AND a.ppsuc = b.ppsuc 
      AND a.ppmda = b.ppmda 
      AND a.ppcta = b.ppcta 
      AND a.ppoper = b.ppoper 
      AND a.ppsbop = b.ppsbop 
      AND a.pptope = b.pptope 
      AND b.d601co = 'S' 
      AND a.pptipo = '' 
      AND a.ppexte = 9 
      AND tp.Ctccli IN (3, 5)	
      AND c.Aooper = coloc.JT73109FPA 
      AND c.Aosuc = coloc.JT73109SUC 
      AND NOT EXISTS ( 		
          SELECT * 	
          FROM as_desistidos AS anu 	
          WHERE c.aocta = anu.hcta 
          AND 	 	c.aooper = anu.hoper 
          AND 	 	 c.aosuc = anu.hsucur) 
   END
go



SET ANSI_NULLS OFF
go

SET QUOTED_IDENTIFIER OFF
go

IF OBJECT_ID('dbo.WASP_M0016_Carga_tabla') IS NOT NULL 
   PRINT '<<< CREATED PROCEDURE dbo.WASP_M0016_Carga_tabla >>>' 
ELSE
   PRINT '<<< FAILED CREATING PROCEDURE dbo.WASP_M0016_Carga_tabla >>>'
go
